package dao.impl;

import dao.StaffsDao;
import entity.Staffs;
import util.DbUtil;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 员工 DAO 接口的实现类 - 基于原生 JDBC 实现
 * @Author lzp
 * @Date 2024-03-06
 */
public class StaffsDaoImpl4jdbc implements StaffsDao{
    @Override
    public List<Staffs> selectAll() {
        List<Staffs> list = new ArrayList<>();

        String sql = "select id,name,age,phone,pos,add_time,update_time from staffs";

        Connection conn = DbUtil.getConnection();

        PreparedStatement pstmt = null;
        ResultSet rst = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rst = pstmt.executeQuery();
            while (rst.next()) {
                int id = rst.getInt(1);
                String name = rst.getString(2);
                int age = rst.getInt(3);
                String phone = rst.getString(4);
                String pos = rst.getString(5);

                LocalDateTime addTime = rst.getTimestamp(6).toLocalDateTime();

                Timestamp updateTimestamp = rst.getTimestamp(7);
                LocalDateTime updateTime = updateTimestamp.toLocalDateTime();

                Staffs staffs = new Staffs();

                staffs.setId(id);
                staffs.setName(name);
                staffs.setAge(age);
                staffs.setPhone(phone);
                staffs.setPos(pos);
                staffs.setAddTime(addTime);
                staffs.setUpdateTime(updateTime);
                list.add(staffs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(rst, pstmt, conn);
        }
        return list;
    }

    @Override
    public int insert(Staffs staffs) {
        int r = 0;
        String sql = "insert into staffs(name,age,phone,pos,add_time,update_time) values(?,?,?,?,?,?)";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, staffs.getName());
            pstmt.setInt(2, staffs.getAge());
            pstmt.setString(3, staffs.getPhone());
            pstmt.setString(4, staffs.getPos());
            pstmt.setTimestamp(5, Timestamp.valueOf(staffs.getUpdateTime()));
            pstmt.setTimestamp(6, Timestamp.valueOf(staffs.getUpdateTime()));
            r = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(null, pstmt, conn);
        }
        return r;
    }

    @Override
    public int delete(int id) {
        int r = 0;
        String sql = "delete from staffs where id=?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            r = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(null, pstmt, conn);
        }
        return r;
    }


    @Override
    public int update(Staffs staffs) {
        int r = 0;
        // 第一：定义要操作数据库的SQL语句
        String sql = "update staffs set name=?,age=?,phone=?,pos=?,add_time=?,update_time=? where id=?";

        // 第二：获取连接对象
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, staffs.getName());
            pstmt.setInt(2, staffs.getAge());
            pstmt.setString(3, staffs.getPhone());
            pstmt.setString(4, staffs.getPos());
            pstmt.setTimestamp(5, Timestamp.valueOf(staffs.getUpdateTime()));
            pstmt.setTimestamp(6, Timestamp.valueOf(staffs.getUpdateTime()));
            pstmt.setInt(7, staffs.getId());

            r = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(null, pstmt, conn);
        }
        return r;
    }

    @Override
    public Staffs selectId(int id) {
        Staffs staffs = null;
        String sql = "select id,name,age,phone,pos,add_time,update_time from staffs where id=?";

        Connection conn = DbUtil.getConnection();

        PreparedStatement pstmt = null;
        ResultSet rst = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rst = pstmt.executeQuery();
            while (rst.next()) {
                int ids = rst.getInt(1);
                String name = rst.getString(2);
                int age = rst.getInt(3);
                String phone = rst.getString(4);
                String pos = rst.getString(5);
                LocalDateTime addTime = rst.getTimestamp(6).toLocalDateTime();
                Timestamp updateTimestamp = rst.getTimestamp(7);
                LocalDateTime updateTime = updateTimestamp.toLocalDateTime();

                staffs = new Staffs();

                staffs.setId(ids);
                staffs.setName(name);
                staffs.setAge(age);
                staffs.setPhone(phone);
                staffs.setPos(pos);
                staffs.setAddTime(addTime);
                staffs.setUpdateTime(updateTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(rst, pstmt, conn);
        }
        return staffs;
    }

    @Override
    public List<Staffs> selectAdPage(int limit, int count) {
        List<Staffs> list = new ArrayList<>();

        // 第一：定义要操作数据库的SQL 语句
        String sql = "select id,name,age,phone,pos,add_time,update_time from staffs limit ?,?";

        // 第二：获取连接对象 - 建立 Java 程序与数据库之间的桥梁
        Connection conn = DbUtil.getConnection();

        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try {
            // 第三：预编译 SQL 语句，并实例化 PreparedStatement 对象
            pstmt = conn.prepareStatement(sql);

            // 第四：填充参数 - 参数来自于方法参数
            // pstmt.setXxx(下标索引,参数值)
            pstmt.setInt(1, limit);
            pstmt.setInt(2, count);

            // 第五：执行SQL语句 - executeUpdate()、executeQuery()
            rst = pstmt.executeQuery();

            // 第六：对结果集中的数据进行处理 - ResultSet - List
            while (rst.next()) {
                // 1.读取当前行中各列的数据
                int id = rst.getInt(1);
                String name = rst.getString(2);
                int age = rst.getInt(3);
                String phone = rst.getString(4);
                String staPos = rst.getString(5);
                LocalDateTime addTime = rst.getTimestamp(6).toLocalDateTime();
                // java.sql.Timestamp -> java.time.LocalDateTime
                LocalDateTime updateTime = rst.getTimestamp(7).toLocalDateTime();

                // 2.实例化实体对象 - 用于封装读取到的数据
                Staffs staffs = new Staffs();

                // 3.具体数据的封装
                staffs.setId(id);
                staffs.setName(name);
                staffs.setAge(age);
                staffs.setPhone(phone);
                staffs.setPos(staPos);
                staffs.setAddTime(addTime);
                staffs.setUpdateTime(updateTime);

                // 把各行的数据添加到List集合中
                list.add(staffs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 第七：关闭相关的对象
            DbUtil.close(rst, pstmt, conn);
        }
        return list;
    }


}
